﻿--================================================================================================
--                                 Change History
--                                 --------------
-- Date          Who     Description
-- -----------   ------  --------------------------------------------------
-- 29/Jun/2024   AndyB   New SystemSetting: WebPlusImpersonateStudentRef 
-- 17/Jul/2024   AndyB   New SystemSetting: ImportServicePollFrequency 
-- 17/Jul/2024   AndyB   New SystemSetting: ImportServiceEmailSendWhen 
-- 17/Jul/2024   AndyB   New SystemSetting: ImportServiceEmailToAddress 
-- 17/Jul/2024   AndyB   New SystemSetting: ImportServiceEmailSubject 
-- 18/Jul/2024   AndyB   New SystemSetting: EmailServer,EmailPort,EmailLoginUser,EmailLoginPassword,EmailSenderAddress,EmailSenderFriendlyName,EmailUseSSL
-- 19/Jul/2024   AndyB   New SystemSetting: WebPlusShowInYearNotes
-- 19/Jul/2024   AndyB   New SystemSetting: WebPlusShowEffortGrades
-- 21/Aug/2024   AndyB   Updated ImportServiceEmailSubject
-- 07/Sep/2024   AndyB   New SystemSetting: ImportServiceSendTestEmail
--------------------------------------------------------------------------------------------------
--select * from SystemSettingValue
--select * from SystemSettingFlag
--------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'WebPlusImpersonateStudentRef')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'WebPlusImpersonateStudentRef', 'The StudentRef to Impersonate (to switch off, set SystemSettingValue to blank)', 'System.String', '999999', '999999', 1)
END

--

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'ImportServicePollFrequency')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'ImportServicePollFrequency', 'The frequency (in minutes) the ImportService polls for jobs', 'System.String', '5', '5', 1)
END

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'ImportServiceEmailSendWhen')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'ImportServiceEmailSendWhen', 'Specifies when emails are sent after executing each job (NONE, EXCEPTION, ALL)', 'System.String', 'EXCEPTION', 'EXCEPTION', 1)
END

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'ImportServiceEmailToAddress')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'ImportServiceEmailToAddress', 'Specifies the email address(es) to send emails to (multiple emails can be separated by ;)', 'System.String', '', '', 1)
END

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'ImportServiceEmailSubject')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'ImportServiceEmailSubject', 'Specifies the email Subject', 'System.String', '{ASSEMBLYNAME} - {OUTCOME} - {JOBDESCRIPTION}', '{ASSEMBLYNAME} - {OUTCOME} - {JOBDESCRIPTION}', 1)
END

--

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailServer')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailServer', 'The Email Server e.g. mail.totalcollege.co.uk', 'System.String', '', '', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailPort')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailPort', 'The Port to use on the Email Server e.g. 587', 'System.String', '587', '587', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailLoginUser')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailLoginUser', 'The UserID to authenticate to the Email Server', 'System.String', '', '', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailLoginPassword')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailLoginPassword', 'The passowrd of the EmailLoginUser (can be clear or prefixed ENCRYPTED:)', 'System.String', 'ENCRYPTED:', 'ENCRYPTED:', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailSenderAddress')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailSenderAddress', 'The email address from which emails are sent (aka From address)', 'System.String', '', '', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailSenderFriendlyName')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailSenderFriendlyName', 'The friendly alias of the EmailSenderAddress', 'System.String', '', '', 1)
END
--
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'EmailUseSSL')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'EmailUseSSL', 'Whether to send emails using SSL (YES or NO)', 'System.String', 'NO', 'NO', 1)
END

--

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'WebPlusShowInYearNotes')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'WebPlusShowInYearNotes', 'Whether to show InYear Notes by default (YES NO)', 'System.String', 'NO', 'NO', 1)
END

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'WebPlusShowEffortGrades')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'WebPlusShowEffortGrades', 'Whether to show Effort Grades by default (YES NO)', 'System.String', 'NO', 'NO', 1)
END


--Added when ported from v1.39
IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'ImportServiceSendTestEmail')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'ImportServiceSendTestEmail', 'When set to YES, the ImportService will send a test email, when it next polls (also when the service is started)', 'System.String', 'NO', 'NO', 1)
END
